package cn.com.shopec.erp.stock.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import cn.com.shopec.erp.common.constant.SZSCMConstant;
import cn.com.shopec.erp.common.orm.impl.HibernateEntityDao;
import cn.com.shopec.erp.common.utils.DateUtil;
import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.stock.commo.ObjectUtil;
import cn.com.shopec.erp.stock.dao.IStockHistoryDao;
import cn.com.shopec.erp.stock.entity.StockHistory;
import cn.com.shopec.erp.stock.vo.StockHistoryVo;

/**
 * 
 * @ClassName: StockHistoryDaoImpl
 * @Description: 库存变更记录Dao接口实现类
 * @author: zengxuezhou
 * @date: 2016年11月28日 下午2:23:35
 */
@Repository
public class StockHistoryDaoImpl extends HibernateEntityDao<StockHistory> implements IStockHistoryDao {
	
	@Override
	public List<StockHistoryVo> getStockHistorys(String criteriaSql, Map<String, Object> values) {
		return getStockHistorys(criteriaSql,values,null,null);
	}
	@Override
	public List<StockHistoryVo> getStockHistorys(String criteriaSql, Map<String, Object> values, Integer pageCount,
			Integer pageSize) {
		String sql = "SELECT S.ID AS id," 
				+ " S.STOCK_NO AS stockNo," 
				+ " S.PROD_NO AS prodNo,"
				+ " S.COMMO_NO AS commoNo," 
				+ " S.SUP_NO AS supNo," 
				+ " S.WH_NO AS whNo, "
				+ " S.STOCK_TYPE AS stockType," 
				+ " S.STOCK_TYPE_NO AS stockTypeNo," 
				+ " S.PUR_NO AS purNo,"
				+ " S.CHANGE_QUANTITY AS changeQuantity,"
				+ " S.ORIGINAL_QUANTITY AS originalQuantity,"
				+ " S.CREATE_TIME AS createTime," 
				+ " WH_NAME AS whName," 
				+ " SUP_NAME AS supName,"
				+ " C.COMMO_NAME AS commoName," 
				+ " GROUP_CONCAT(SVO.OPTION_NAME) AS specification"
				+ " FROM TBL_STOCK_HISTORY S"
				+ " LEFT JOIN TBL_WAREHOUSE TW ON TW.WH_NO = S.WH_NO"
				+ " LEFT JOIN TBL_SUPPLIER_INFO TSI ON TSI.CODE = S.SUP_NO"
				+ " LEFT JOIN TBL_PRODUCT PROD ON PROD.PROD_NO = S.PROD_NO"
				+ " LEFT JOIN TBL_COMMODITY C ON C.COMMO_NO = S.COMMO_NO "
				+ " LEFT OUTER JOIN TBL_PROD_SPEC_VALUE PSV ON PSV.PROD_NO = S.PROD_NO"
				+ " LEFT OUTER JOIN TBL_SPEC_VAL_OPTION SVO ON SVO.OPTION_NO = PSV.VAL_OPTION_NO"
				+ " WHERE TW.DELETE_FLAG = 1 AND TSI.DELETE_FLAG = 1"
				+ criteriaSql 
				+ " GROUP BY S.ID ORDER BY S.PROD_NO DESC,S.WH_NO DESC, S.CREATE_TIME DESC";

		Query query = getSession().createSQLQuery(sql).addScalar("id", StandardBasicTypes.STRING)
				.addScalar("stockNo", StandardBasicTypes.STRING).addScalar("stockType", StandardBasicTypes.INTEGER)
				.addScalar("stockTypeNo", StandardBasicTypes.STRING)
				.addScalar("changeQuantity", StandardBasicTypes.LONG)
				.addScalar("originalQuantity", StandardBasicTypes.LONG).addScalar("commoNo", StandardBasicTypes.STRING)
				.addScalar("whNo", StandardBasicTypes.STRING).addScalar("prodNo", StandardBasicTypes.STRING)
				.addScalar("supNo", StandardBasicTypes.STRING).addScalar("purNo", StandardBasicTypes.STRING)
				.addScalar("whName", StandardBasicTypes.STRING).addScalar("supName", StandardBasicTypes.STRING)
				.addScalar("commoName", StandardBasicTypes.STRING).addScalar("specification", StandardBasicTypes.STRING)
				.addScalar("createTime", StandardBasicTypes.TIMESTAMP);
		query.setProperties(values);
		if (ObjectUtil.isNotNull(pageCount) && ObjectUtil.isNotNull(pageSize)) {
			query.setFirstResult(pageCount).setMaxResults(pageSize);
		}
		query.setResultTransformer(Transformers.aliasToBean(StockHistoryVo.class));
		@SuppressWarnings("unchecked")
		List<StockHistoryVo> stockHistoryVo = query.list();
		return stockHistoryVo;
	}

	@Override
	public int getProductsCountByStockNo(String criteriaSql, Map<String, Object> values) {
		String sql = "SELECT COUNT(1) FROM TBL_STOCK_HISTORY S ";
		if (ObjectUtil.isNotNull(values.get("commoName"))) {
			sql += " LEFT JOIN TBL_COMMODITY C ON C.COMMO_NO = S.COMMO_NO ";
		}
		sql += " WHERE S.DELETED_FLAG = " + SZSCMConstant.DELETE_FLAG_NO;
		sql += criteriaSql;
		Object count = createSQLQuery(sql, values).uniqueResult();
		return ObjectUtil.toInteger(count);
	}

	@Override
	public String getQueryCriteriaSql(StockHistoryVo stockHistory, Map<String, Object> values) {
		StringBuilder criteriaSql = new StringBuilder();
		if (StringUtil.isExist(stockHistory.getStockNo())) {// 出入库单编号
			criteriaSql.append(" AND S.STOCK_NO LIKE :stockNo");
			values.put("stockNo", "%" + stockHistory.getStockNo() + "%");
		}
		if (StringUtil.isExist(stockHistory.getStockTypeNo())) {// 单号
			criteriaSql.append(" AND S.STOCK_TYPE_NO LIKE :stockTypeNo");
			values.put("stockTypeNo", "%" + stockHistory.getStockTypeNo() + "%");
		}
		if (ObjectUtil.isNotNull(stockHistory.getStockType())) {// 出入库类型
			criteriaSql.append(" AND S.STOCK_TYPE = :stockType");
			values.put("stockType", stockHistory.getStockType());
		}
		if (StringUtil.isExist(stockHistory.getCommoNo())) {// 商品编号
			criteriaSql.append(" AND S.COMMO_NO LIKE :commoNo ");
			values.put("commoNo", "%" + stockHistory.getCommoNo() + "%");
		}
		if (StringUtil.isExist(stockHistory.getWhNo())) {// 仓库编号
			criteriaSql.append(" AND S.WH_NO = :whNo");
			values.put("whNo", stockHistory.getWhNo());
		}
		if (StringUtil.isExist(stockHistory.getBeginTime())) {// 出入库起始时间
			criteriaSql.append(" AND S.CREATE_TIME >= :beginTime");
			values.put("beginTime", DateUtil.parseTimesTampDate(stockHistory.getBeginTime()));
		}
		if (StringUtil.isExist(stockHistory.getEndTime())) {// 出入库结束时间
			criteriaSql.append(" AND S.CREATE_TIME <= :endTime");
			values.put("endTime", DateUtil.parseTimesTampDate(stockHistory.getEndTime()));
		}
		if (StringUtil.isExist(stockHistory.getCommoName())) {// 商品名称
			criteriaSql.append(" AND C.COMMO_NAME LIKE :commoName ");
			values.put("commoName", "%" + stockHistory.getCommoName() + "%");
		}
		if (StringUtil.isExist(stockHistory.getPurNo())) {// 采购单号
			criteriaSql.append(" AND S.PUR_NO LIKE :purNo ");
			values.put("purNo", "%" + stockHistory.getPurNo() + "%");
		}
		return criteriaSql.toString();
	}
}
